﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using WebBanThuoc.DTO;

namespace WebBanThuoc.DAO
{
    public class TinTuc_DAO
    {
        private SqlConnection connection;
        //public bool hasData { set; get; }
        public TinTuc_DAO(SqlConnection connection)
        {
            this.connection = connection;
        }

        private TinTuc getBy(string whereCom, string orderCom)
        {
            TinTuc data = null;
            string comStr = "Select * from [TinTuc]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    data = new TinTuc();
                    dap.DaReader.Read();

                    data.IDTinTuc = Convert.ToInt32(dap.DaReader["IDTinTuc"]);
                    data.TenTinTuc = dap.DaReader["TenTinTuc"].ToString();
                    data.AnhNho = dap.DaReader["AnhNho"].ToString();
                    data.NoiDungSoLuoc = dap.DaReader["NoiDungSoLuoc"].ToString();
                    data.NoiDung = dap.DaReader["NoiDung"].ToString();
                    data.NgayDang = Convert.ToDateTime(dap.DaReader["NgayDang"]);
                    data.SoLuotXem = Convert.ToInt32(dap.DaReader["SoLuotXem"]);
                    data.MaDanhMuc = Convert.ToInt32(dap.DaReader["MaDanhMuc"]);
                    data.NoiDungGuiEmail = dap.DaReader["NoiDungGuiEmail"].ToString();
                    data.UserName = dap.DaReader["UserName"].ToString();                    
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public TinTuc getByKey(int IDTinTuc)
        {
            return getBy(" [IDTinTuc]=" + IDTinTuc, " [IDTinTuc] asc");
        }

        public int createIDTinTuc()
        {
            int result = 1;
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, "SELECT [dbo].[fnc_CreateNew_IDTinTuc] () as col");
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    dap.DaReader.Read();
                    result = Convert.ToInt32(dap.DaReader["col"]);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public int add(TinTuc obj)
        {
            string comQuery = "INSERT INTO [TinTuc] ([IDTinTuc],[TenTinTuc],[AnhNho]"
                + ",[NoiDungSoLuoc],[NoiDung],[MaDanhMuc],[UserName],[NoiDungGuiEmail])"
                + " VALUES (@IDTinTuc,@TenTinTuc,@AnhNho,@NoiDungSoLuoc,@NoiDung,@MaDanhMuc"
                + ",@UserName, @NoiDungGuiEmail)";
            try
            {
                IDataProvider dap = new DataProvider(connection);
                if (obj.IDTinTuc == null || obj.IDTinTuc <= 0)
                {
                    obj.IDTinTuc = createIDTinTuc();
                }
                dap.AddParameters("@IDTinTuc", obj.IDTinTuc, System.Data.SqlDbType.Int);
                dap.AddParameters("@TenTinTuc", obj.TenTinTuc, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@AnhNho", obj.AnhNho, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@NoiDungSoLuoc", obj.NoiDungSoLuoc, System.Data.SqlDbType.NText);
                dap.AddParameters("@NoiDung", obj.NoiDung, System.Data.SqlDbType.NText);
                dap.AddParameters("@MaDanhMuc", obj.MaDanhMuc, System.Data.SqlDbType.Int);
                dap.AddParameters("@NoiDungGuiEmail", obj.NoiDungGuiEmail, System.Data.SqlDbType.NText);
                dap.AddParameters("@UserName", obj.UserName, System.Data.SqlDbType.NVarChar);                

                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }
        private int delete(string whereCom)
        {
            string comUpdate = "DELETE FROM [TinTuc] ";
            if (whereCom.Trim() != "")
                comUpdate += " where " + whereCom;
            try
            {
                IDataProvider dap = new DataProvider(this.connection);
                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comUpdate);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int deleteByKey(int IDTinTuc)
        {
            return delete(" [IDTinTuc]=" + IDTinTuc.ToString());
        }

        private int update(TinTuc obj, string whereCom)
        {
            string comQuery = "UPDATE [TinTuc] SET [IDTinTuc]=@IDTinTuc, [TenTinTuc]=@TenTinTuc, [AnhNho]=@AnhNho"
                + ", [NoiDungSoLuoc]=@NoiDungSoLuoc, [NoiDung]=@NoiDung"
                + ", [MaDanhMuc]=@MaDanhMuc, [UserName]=@UserName, [NoiDungGuiEmail]=@NoiDungGuiEmail";
            if (whereCom.Trim() != "")
                comQuery += " where " + whereCom;
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.AddParameters("@IDTinTuc", obj.IDTinTuc, System.Data.SqlDbType.Int);
                dap.AddParameters("@TenTinTuc", obj.TenTinTuc, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@AnhNho", obj.AnhNho, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@NoiDungSoLuoc", obj.NoiDungSoLuoc, System.Data.SqlDbType.NText);
                dap.AddParameters("@NoiDung", obj.NoiDung, System.Data.SqlDbType.NText);
                dap.AddParameters("@MaDanhMuc", obj.MaDanhMuc, System.Data.SqlDbType.Int);
                dap.AddParameters("@UserName", obj.UserName, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@NoiDungGuiEmail", obj.NoiDungGuiEmail, System.Data.SqlDbType.NVarChar);                

                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int updateByKey(TinTuc tintuc, int IDTinTuc)
        {
            return update(tintuc, " [IDTinTuc]=" + IDTinTuc.ToString());
        }

        private List<TinTuc> getList_Paging_By(string whereCom, int pageIndex, int pageSize)
        {
            int begin = (pageIndex - 1) * pageSize + 1;
            int end = pageIndex * pageSize;
            List<TinTuc> data = new List<TinTuc>();

            if (whereCom.Trim() != "")
            {
                whereCom = " where " + whereCom;
            }

            string comStr = "Select * from (select *, ROW_NUMBER() Over(order by [NgayDang] desc) as rowNumber from [TinTuc] " + whereCom + ") sp ";
            comStr += " where [rowNumber] between " + begin.ToString() + " and " + end.ToString();

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        TinTuc obj = new TinTuc();

                        obj.IDTinTuc = Convert.ToInt32(dap.DaReader["IDTinTuc"]);
                        obj.TenTinTuc = dap.DaReader["TenTinTuc"].ToString();
                        obj.AnhNho = dap.DaReader["AnhNho"].ToString();
                        obj.NoiDungSoLuoc = dap.DaReader["NoiDungSoLuoc"].ToString();
                        obj.NoiDung = dap.DaReader["NoiDung"].ToString();
                        obj.NgayDang = Convert.ToDateTime(dap.DaReader["NgayDang"]);
                        obj.SoLuotXem = Convert.ToInt32(dap.DaReader["SoLuotXem"]);
                        obj.MaDanhMuc = Convert.ToInt32(dap.DaReader["MaDanhMuc"]);
                        obj.NoiDungGuiEmail = dap.DaReader["NoiDungGuiEmail"].ToString();
                        obj.UserName = dap.DaReader["UserName"].ToString();

                        data.Add(obj);

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }
        public List<TinTuc> getList_Paging_DM(int MaDanhMuc, int pageIndex, int pageSize)
        {
            return getList_Paging_By("[MaDanhMuc] = " + MaDanhMuc, pageIndex, pageSize);
        }
        public int totalNumberOfPages_All(int pageSize)
        {
            return totalNumberOfPagesBy("", pageSize);
        }
        public int totalNumberOfPages_All(int MaDanhMuc, int pageSize)
        {
            return totalNumberOfPagesBy("[MaDanhMuc] = " + MaDanhMuc, pageSize);
        }
        private int totalNumberOfPagesBy(string whereCom, int pageSize)
        {
            int result = 0;
            try
            {
                string comStr = "select count([IDTinTuc]) as col from [TinTuc] ";
                if (whereCom.Trim() != "")
                {
                    comStr += " where " + whereCom;
                }


                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    dap.DaReader.Read();
                    result = Convert.ToInt32(dap.DaReader["col"]);
                    if (result % pageSize != 0)
                    {
                        result = result / pageSize + 1;
                    }
                    else
                    {
                        result = result / pageSize;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        // get top 10
        private List<TinTuc> getList_By(string whereCom, string orderCom)
        {
            List<TinTuc> data = new List<TinTuc>();

            string comStr = "Select TOP 10 * from [TinTuc]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        TinTuc obj = new TinTuc();

                        obj.IDTinTuc = Convert.ToInt32(dap.DaReader["IDTinTuc"]);
                        obj.TenTinTuc = dap.DaReader["TenTinTuc"].ToString();
                        obj.AnhNho = dap.DaReader["AnhNho"].ToString();
                        obj.NoiDungSoLuoc = dap.DaReader["NoiDungSoLuoc"].ToString();
                        obj.NoiDung = dap.DaReader["NoiDung"].ToString();
                        obj.NgayDang = Convert.ToDateTime(dap.DaReader["NgayDang"]);
                        obj.SoLuotXem = Convert.ToInt32(dap.DaReader["SoLuotXem"]);
                        obj.MaDanhMuc = Convert.ToInt32(dap.DaReader["MaDanhMuc"]);
                        obj.NoiDungGuiEmail = dap.DaReader["NoiDungGuiEmail"].ToString();
                        obj.UserName = dap.DaReader["UserName"].ToString();


                        data.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }
        public List<TinTuc> getList_Top(int MaDanhMuc)
        {
            return getList_By(" [MaDanhMuc] =" + MaDanhMuc, " [NgayDang] desc");

        }


        private List<TinTuc> getListBy(string whereCom, string orderCom)
        {
            List<TinTuc> data = new List<TinTuc>();

            string comStr = "Select * from [TinTuc]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        TinTuc obj = new TinTuc();

                        obj.IDTinTuc = Convert.ToInt32(dap.DaReader["IDTinTuc"]);
                        obj.TenTinTuc = dap.DaReader["TenTinTuc"].ToString();
                        obj.AnhNho = dap.DaReader["AnhNho"].ToString();
                        obj.NoiDungSoLuoc = dap.DaReader["NoiDungSoLuoc"].ToString();
                        obj.NoiDung = dap.DaReader["NoiDung"].ToString();
                        obj.NgayDang = Convert.ToDateTime(dap.DaReader["NgayDang"]);
                        obj.SoLuotXem = Convert.ToInt32(dap.DaReader["SoLuotXem"]);
                        obj.MaDanhMuc = Convert.ToInt32(dap.DaReader["MaDanhMuc"]);
                        obj.NoiDungGuiEmail = dap.DaReader["NoiDungGuiEmail"].ToString();
                        obj.UserName = dap.DaReader["UserName"].ToString();

                        data.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }
        public List<TinTuc> getListByKey(string TenTinTuc, int MaDanhMuc)
        {
            string comQ = "";
            comQ += " [TenTinTuc] like N'%" + TenTinTuc + "%' ";
            if (MaDanhMuc > 0)
            {
                comQ += " and [MaDanhMuc]=" + MaDanhMuc;
            }
            return getListBy(comQ, " [NgayDang] desc");

        }
    }
}